package dbManagement;

import com.mysql.jdbc.ResultSet;
import core.PatientBean;
import hirondelle.date4j.DateTime;
import hirondelle.date4j.DateTime.DayOverflow;
import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.SQLException;
import javax.xml.parsers.ParserConfigurationException;
import logManagement.Log4k;
import objects.*;
import org.xml.sax.SAXException;
import ws.*;

/**
 *
 * @author loris.dallago
 *
 */
public class dbManager{
    private final dbConnector dbConn = new dbConnector();

    public dbManager() {
        /* does nothing special: creation of the dbConnector is done here above */
    }

    public void releaseConnection(){
        dbConn.closeConnection();
    }

    private String dbDate(DateTime date){
        final String dateFormat = "YYYY-MM-DD hh-mm-ss";
        return sqlString(date.format(dateFormat));
    }
    
    private String DBtime(){
        return subQuery("SELECT NOW() AS n_o_w");
    }

    private String DBaddTime(int sec) {
        return subQuery("SELECT NOW() + INTERVAL " + sec + " SECOND");
    }
    
    private String subQuery(String query){
        return "(" + query + ")";
    }
    
    private String subQuery(String query, String alias){
        return "(" + query + ") as " + alias;
    }
    
    private String sqlString(String str){
        str = str.replace("'", "''");
        return "'" + str + "'";
    }
    
    /**
     * This function returns a single tuple corresponding to the input parameters.
     * If a user is found inside the db matching the given input parameters then
     * a tuple is returned, otherwise an empty result set is returned.
     */
    public ResultSet matchingUser(String userName, String pwd, userKind type){
        ResultSet res;
        boolean isPatient = (type == userKind.PATIENT);
        boolean is_usl = (type == userKind.USL);
        String userTableStr = ((isPatient) ? "patients" : "health_workers");
        String command = "SELECT * FROM " + userTableStr + " WHERE "
                + "username = " + sqlString(userName) + " AND "
                + "password = " + sqlString(pwd) + " AND "
                + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
        res = dbConn.executeQuery(command);        
        return res;
    }
    
    public ResultSet getPatients(int doctor_id){
        ResultSet res;        
        String command = "SELECT * FROM patients WHERE refDoctor_id = " + doctor_id;
        res = dbConn.executeQuery(command);        
        return res;
    }
    
    public ResultSet getAllPatients(){
        ResultSet res;        
        String command = "SELECT * FROM patients";
        res = dbConn.executeQuery(command);        
        return res;
    }
    
    public ResultSet getAllDoctors(){
        ResultSet res;
        String command = "SELECT * FROM health_workers WHERE NOT is_usl";
        res = dbConn.executeQuery(command);
        return res;
    }

    public boolean existsThePatient(int patient_id){
        boolean res = false;
        try {
            String command = "SELECT * FROM patients WHERE patient_id = " + patient_id;
            ResultSet sqlSet = dbConn.executeQuery(command);
            res = sqlSet.first();
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }    
    
    private boolean isMyPatient(int doctor_id, int patient_id){
        boolean res = false;
        try {            
            ResultSet resSet;
            String command = "SELECT refDoctor_id FROM patients WHERE patient_id = " + patient_id;
            resSet = dbConn.executeQuery(command);        
            if(resSet.first()){
                res = (doctor_id == resSet.getInt("refDoctor_id")); 
            } else {
                /* Paziente non trovato */
                res = false;
            }
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    public ResultSet getPatientByID(int patient_id, userKind ukind){
        ResultSet res = null;
        if (ukind == userKind.PATIENT){
            String command = "SELECT * FROM patients WHERE patient_id = " + patient_id;
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be in a PATIENT session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    public ResultSet getPatientByID(int doctor_id, int patient_id, userKind ukind) throws notMyPatientException{
        ResultSet res = null;     
        if (ukind == userKind.DOCTOR){
            if (!isMyPatient(doctor_id, patient_id)){
                throw new notMyPatientException();
            }
            String command = "SELECT * FROM patients WHERE patient_id = " + patient_id;
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be either in in a DOCTOR or a USL session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
                    
        return res;
    }
    
    public ResultSet getPatientHistory(int patient_id, userKind ukind){
        ResultSet res = null;        
        if (ukind == userKind.PATIENT){
            String command = "SELECT * FROM medical_records WHERE "
                    + "patient_id = " + patient_id + " AND visit_done";
            res = dbConn.executeQuery(command);        
        } else {
            String msg = "You have to be in a PATIENT session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    public ResultSet getPatientHistory(int doctor_id, int patient_id, userKind ukind) throws notMyPatientException{
        ResultSet res = null;  
        if (ukind == userKind.DOCTOR){
            if (!isMyPatient(doctor_id, patient_id)){
                throw new notMyPatientException();
            }
            String command = "SELECT * FROM medical_records WHERE "
                    + "patient_id = " + patient_id + " AND visit_done";
            res = dbConn.executeQuery(command);        
        } else {
            String msg = "You have to be in a DOCTOR session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    /* This function retrieves all the scheduled visits a doctor should perform */
    public ResultSet getMyScheduledVisits(int doctor_id, userKind ukind){
        ResultSet res = null;
        if (ukind == userKind.DOCTOR){
            String command = "SELECT * FROM medical_records "
                    + "WHERE doctor_id = " + doctor_id + " AND "
                    + "NOT visit_done AND is_notified";
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be in a DOCTOR session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }    
    
    /* This function retrieves the next visits a patient should attend */
    public ResultSet getNextVisits(int patient_id, userKind ukind){
        ResultSet res = null;
        if (ukind == userKind.PATIENT){
            String command = "SELECT * FROM medical_records "
                    + "WHERE patient_id = " + patient_id + " AND "
                    + "NOT visit_done AND is_notified";
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be in a PATIENT session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    /* This function retrieves the next LIMIT visits a patient should attend */
    public ResultSet getNextNVisits(int patient_id, userKind ukind, int LIMIT){
        ResultSet res = null;
        if (ukind == userKind.PATIENT){
            String command = "SELECT * FROM medical_records "
                    + "WHERE patient_id = " + patient_id + " AND "
                    + "NOT visit_done AND is_notified "
                    + "ORDER BY date LIMIT " + LIMIT;
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be in a PATIENT session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
   
    /* This function retrieves the next visits a patient should attend */
    public ResultSet getNextVisits(int doctor_id, int patient_id, userKind ukind) throws notMyPatientException{
        ResultSet res = null;
        if (ukind == userKind.DOCTOR){
            if (!isMyPatient(doctor_id, patient_id)){
                throw new notMyPatientException();
            }
            String command = "SELECT * FROM medical_records "
                    + "WHERE patient_id = " + patient_id + " AND "
                    + "NOT visit_done AND is_notified";
            res = dbConn.executeQuery(command);
        } else {
            String msg = "You have to be in a DOCTOR session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    /* Prepare the system to admonish bad guys that skipped their examinations:
     * medical_records is examined in order to set all the skipped
     * examinations to a new date (Macro.LAPSE_AFTER_NOTIFICATION seconds after)
     * and put them through being notified soon.  
     * Since we are using executeStatementNoLock, the caller of this function
     * must be in a synchronized context.
     */
    private void updateMedicalRecordsToSkippedVisits(){
        String command = "UPDATE medical_records SET "
                + "date = " + DBaddTime(Macro.LAPSE_AFTER_NOTIFICATION) + ", "       
                + "is_notified = FALSE "
                + "WHERE NOT visit_done AND date <= " + DBtime();
        dbConn.executeStatementNoLock(command);
    }
    
    public void sumAReminder(int record_id){
        String command = "UPDATE medical_records "
                + "SET reminder = reminder + 1, is_notified = TRUE "
                + "WHERE record_id = " + record_id;
        dbConn.executeStatement(command);
    }
    
    /* This function retrieves all the visits skipped at least once, in
     * decreasing order of reminders
     */
    public ResultSet getSkippedVisits(userKind ukind){        
        ResultSet res = null;
        if (ukind == userKind.USL){
            synchronized(dbConnector.mutex){
                updateMedicalRecordsToSkippedVisits();
                
                String command = "SELECT * FROM medical_records "
                        + "WHERE NOT visit_done AND reminder > 0 "
                        + "AND NOT is_notified "
                        + "ORDER BY reminder DESC";
                res = dbConn.executeQuery(command);
            }
        } else {
            String msg = "You have to be in a USL session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    /* Whenever a new tuple of the usl_schedule table is acknowledged, each
     * patient has to be put through the hypothesis of a new visit.
     * Moreover, PERIODIC examinations are updated.
     * Since we are using executeStatementNoLock, the caller of this function
     * must be in a synchronized context.
     */
    private void updateMedicalRecordsWithNewScheduleInformation(){
        String new_schedules = "SELECT * FROM usl_schedule WHERE is_new";
        String SQnew_schedules = subQuery(new_schedules, "NS");
        
        String periodicDate = DBaddTime(Macro.MINIMUM_LAPSE);
        String periodicKind = "'VACCINATION'";
        String periodicCond = "kind = 'PERIODIC'";
        String information1 = "SELECT patient_id, refDoctor_id, program_id, "
                + periodicDate + " as D, " + periodicKind + " as K "
                + "FROM patients, " + SQnew_schedules + " WHERE " + periodicCond;
        String SQinformation1 = subQuery(information1, "SQ1");
        
        String programmedDate = "lapseordate";
        String programmedKind = "'PROGRAMMED'";
        String programmedCond = "kind = 'PROGRAMMED'";
        String information2 = "SELECT patient_id, refDoctor_id, program_id, "
                + programmedDate + " as D, " + programmedKind + " as K "
                + "FROM patients, " + SQnew_schedules + " WHERE " + programmedCond;
        String SQinformation2 = subQuery(information2, "SQ2");
        
        String bornProgrammedDate = "birthDate + INTERVAL lapseordate SECOND";
        String bornProgrammedKind = "'PROGRAMMED'";
        String bornProgrammedCond = "kind = 'BORN_PROGRAMMED' AND "
                + "enacting_date <= birthDate";
        String information3 = "SELECT patient_id, refDoctor_id, program_id, "
                + bornProgrammedDate + " as D, " + bornProgrammedKind + " as K "
                + "FROM patients, " + SQnew_schedules + " WHERE " + bornProgrammedCond;
        String SQinformation3 = subQuery(information3, "SQ3");
                
        /* UNION keyword cannot be used with parenthesied subqueries */
        String information = information1 + " UNION " + information2
                + " UNION " + information3;
        String SQinformation = subQuery(information, "INFO");
        String command = "INSERT INTO medical_records(patient_id, doctor_id, program_id, date, kind) "
                + "SELECT * FROM " + SQinformation;
        dbConn.executeStatementNoLock(command);

        /* Let the system know that the selected usl_schedules have been dealt.
         * Note that we are assuming that no new schedule is being inserted inside
         * the db between the first and second statement issue (the
         * synchronization system ensures this).
         * Also notice that once is_new has been toggled, it does not need to be
         * toggled anymore.
         */
        command = "UPDATE usl_schedule "
                + "SET is_new = FALSE "
                + "WHERE is_new";
        dbConn.executeStatementNoLock(command);
        
        
        /* After having updated the medical_records table with the new schedules,
         * also update the dates for the correctly attended periodic examinations,
         * should it be necessary.
         * Medical examinations that have been skipped are dealt (updated) elsewhere.
         */        
        String lapse = "SELECT lapseordate FROM usl_schedule "
                + "WHERE usl_schedule.program_id = MR.program_id";
        String SQlapse = subQuery(lapse);
        String uslKind = "SELECT kind FROM usl_schedule "
                + "WHERE usl_schedule.program_id = MR.program_id";
        String SQuslKind = subQuery(uslKind);
        command = "UPDATE medical_records MR "
                + "SET date = date + INTERVAL " + SQlapse + " SECOND" + ", "
                + "reminder = 0" + ", "
                + "visit_done = FALSE" + ", "
                + "is_notified = FALSE "
                + "WHERE " + SQuslKind + " = 'PERIODIC' AND visit_done";
        dbConn.executeStatementNoLock(command);
    }

    /* First of all, this function checks if some new schedule has been inserted
     * into the db. In that case medical_records table gets a new entry for
     * each patient for each new schedule. Moreover, correctly attended
     * periodic visits are updated to a new date. In this process is fundamental
     * the continuity for the usl users in accessing the page which calls
     * this function (i.e. it is important for the correct work of the system
     * to update medical_records frequently).
     * Finally, the examinations to be reminded are retrieved.
     */
    public ResultSet retrieveVisitsToRemind(int interval, userKind ukind){
        ResultSet res = null;
        String command = "";
        if (ukind == userKind.USL){
            synchronized(dbConnector.mutex){
                updateMedicalRecordsWithNewScheduleInformation();
                
                String condition1 = "NOT visit_done";
                String condition21 = DBtime() + " <= date AND date <= " + DBaddTime(interval);
                String condition22 = "NOT is_notified";
                String condition2 = "(" + condition21 + ")" + " OR " + "(" + condition22 + ")";
                String condition = "(" + condition1 + ")" + " AND " + "(" + condition2 + ")";
                command = "SELECT * FROM medical_records WHERE " + condition;
                res = dbConn.executeQuery(command);

                /* Let the system know that the selected medical_records have been
                 * notified. Note that we are assuming that no new non notified
                 * records is being inserted inside the db between the first query
                 * and the statement issue (the synchronization system ensures this).
                 */
                command = "UPDATE medical_records "
                        + "SET is_notified = TRUE "
                        + "WHERE NOT is_notified";
                dbConn.executeStatementNoLock(command);
            }
        } else {
            String msg = "You have to be in a USL session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return res;
    }
    
    
    /* returns tuples from a join between medical_records and patient */
    public ResultSet getNotifiedVisitsByCell(String cell_number){
        ResultSet res;
        String command = "SELECT * FROM medical_records JOIN patients "
                + "ON medical_records.patient_id = patients.patient_id "
                + "WHERE cell_number = " + sqlString(cell_number) + " AND "
                + "NOT visit_done AND is_notified AND date >= " + DBtime();
        res = dbConn.executeQuery(command);
        
        return res;
    }
    
    public ResultSet getMyDoctorNameByCell(String cell_number){
        ResultSet res = null ;
        String doctorId = "SELECT refDoctor_id FROM patients "
                + "WHERE cell_number = " + sqlString(cell_number);
        String SQdoctorId = subQuery(doctorId);
        
        String command = "SELECT * FROM health_workers WHERE id = " + SQdoctorId + " AND NOT is_usl";
        res = dbConn.executeQuery(command);
        return res;
    }
    
    /* Return a two tuples ResultSet corresponding to the nearest (wrt time)
     * couple of examinations that are going to be scheduled but they are not yet.
     * They can either be PERIODIC examinations or just-inserted PROGRAMMED examinations.
     * The returned ResultSet is so structured:
     * <date, reminder, kind, program_id, description>
     */    
    public ResultSet getNextTwoYetUnscheduledVisits(String cell_number){        
        ResultSet res;
        
        updateMedicalRecordsWithNewScheduleInformation();
        
        String patientId = "SELECT patient_id FROM patients "
                + "WHERE cell_number = " + sqlString(cell_number);
        String SQpatientId = subQuery(patientId);
        
        /* select from the new schedules the non periodic ones */
        String information1 = "SELECT date, reminder, medical_records.kind AS kind, "
                + "usl_schedule.program_id AS program_id, description "
                + "FROM medical_records JOIN usl_schedule "
                + "ON medical_records.program_id = usl_schedule.program_id "
                + "WHERE patient_id = " + SQpatientId + " AND "
                + "usl_schedule.kind <> 'PERIODIC' AND NOT is_notified "
                + "AND date >= " + DBtime();
        String SQinformation1 = subQuery(information1);
        
        /* Amongst PERIODIC examinations within medical_records,
         * if they are found to be already notified, select the following twos,
         * otherwise if they have not been notified yet, select the current
         * tuple and its following one.
         */
        String information211 = "SELECT date + INTERVAL lapseordate SECOND AS date, "
                + "reminder, medical_records.kind AS kind, "
                + "usl_schedule.program_id AS program_id, description "
                + "FROM medical_records JOIN usl_schedule "
                + "ON medical_records.program_id = usl_schedule.program_id "
                + "WHERE patient_id = " + SQpatientId + " AND "
                + "usl_schedule.kind = 'PERIODIC' AND is_notified";
        String SQinformation211 = subQuery(information211);
        
        String information212 = "SELECT date + INTERVAL 2*lapseordate SECOND AS date, "
                + "reminder, medical_records.kind AS kind, "
                + "usl_schedule.program_id AS program_id, description "
                + "FROM medical_records JOIN usl_schedule "
                + "ON medical_records.program_id = usl_schedule.program_id "
                + "WHERE patient_id = " + SQpatientId + " AND "
                + "usl_schedule.kind = 'PERIODIC' AND is_notified";
        String SQinformation212 = subQuery(information212);
        
        String information221 = "SELECT date, "
                + "reminder, medical_records.kind AS kind, "
                + "usl_schedule.program_id AS program_id, description "
                + "FROM medical_records JOIN usl_schedule "
                + "ON medical_records.program_id = usl_schedule.program_id "
                + "WHERE patient_id = " + SQpatientId + " AND "
                + "usl_schedule.kind = 'PERIODIC' AND NOT is_notified";
        String SQinformation221 = subQuery(information221);
        
        String information222 = "SELECT date + INTERVAL lapseordate SECOND AS date, "
                + "reminder, medical_records.kind AS kind, "
                + "usl_schedule.program_id AS program_id, description "
                + "FROM medical_records JOIN usl_schedule "
                + "ON medical_records.program_id = usl_schedule.program_id "
                + "WHERE patient_id = " + SQpatientId + " AND "
                + "usl_schedule.kind = 'PERIODIC' AND NOT is_notified";
        String SQinformation222 = subQuery(information222);
        
        /* Merge everything */
        /* UNION keyword cannot be used with parenthesied subqueries */
        String information = SQinformation1 + " "
                + "UNION " + SQinformation211 + " "
                + "UNION " + SQinformation212 + " "
                + "UNION " + SQinformation221 + " "
                + "UNION " + SQinformation222 + " ";
        String SQinformation = subQuery(information, "INFO");
        
        /* Select only the nearest (wrt time) */
        String command = "SELECT * FROM " + SQinformation + " "
                + "ORDER BY date LIMIT 2";
        res = dbConn.executeQuery(command);
        
        return res;
    }
            
    
    /*
     * 
     * @param patient_id the patient who shall attend the examination
     * @param doctor_id the doctor who adds the visit to the db
     * @param program_id the program_id corresponding to the usl_schedule. If
     * the examination has not been withdrawn from the usl_schedule then
     * program_id should be < 0 (say -1)
     * @param date the date in which the patient shall be seen.
     * @param kind the examination kind. If kind is PATIENT_REQUEST then
     * the current db time is taken as a date
     * @param ukind always to be userKind.DOCTOR in order to get the
     * user programmer be aware of being in a doctor session at this point.
     * @throws notMyPatientException if patient_id does not correspond to doctor_id
     * @return the id of the inserted visit
     */
    public int addVisit(int patient_id, int doctor_id, int program_id, DateTime date, visitKind kind, userKind ukind) throws notMyPatientException{
        int ret = -1;
        
        if ((ukind == userKind.DOCTOR) || (ukind == userKind.USL)){
            if ((!isMyPatient(doctor_id, patient_id)) && (kind != visitKind.PATIENT_REQUEST)){
                throw new notMyPatientException();
            }
            String sqlDate = dbDate(date);
            
            synchronized(dbConnector.mutex){
                String command;
                command = "INSERT INTO medical_records(patient_id, doctor_id, program_id, date, kind) "
                        + "VALUES ("
                        + patient_id + ", "
                        + doctor_id + ", "
                        + ((program_id < 0) ? "NULL" : program_id) + ", "
                        + sqlDate + ", "
                        + sqlString(kind.toString())
                        + ")";
                dbConn.executeStatementNoLock(command);
                
                command = "SELECT max(record_id) AS m FROM medical_records";
                ResultSet sqlSet = dbConn.executeQuery(command);
                try {
                    if (sqlSet.first()){
                        ret = sqlSet.getInt("m");
                    } else {
                        String err = "addVisit: nessuna visita trovata "
                                + "nella db table medical_records";
                        Log4k.warn(this.getClass().getName(), err);
                    }                     
                } catch (SQLException ex) {
                    Log4k.error(this.getClass().getName(), ex.getMessage());
                }
            }
        } else {
            String msg = "You have to be in a either in a DOCTOR or in a USL session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
        return ret;
    }
 
    /* This function has to be used by a doctor to save the report inside the db
     * and to set the visit to done.
     * doctor_id, patient_id and ukind are only used for code checking purposes.
     */
    public void doExamine(int doctor_id, int patient_id, int visit_id, String report, userKind ukind) throws notMyPatientException{
        String command;
        if (ukind == userKind.DOCTOR){
            if (!isMyPatient(doctor_id, patient_id)){
                throw new notMyPatientException();
            }
            command = "UPDATE medical_records "
                    + "SET report = " + sqlString(report) + ", " + "visit_done = TRUE "
                    + "WHERE record_id = " + visit_id;
            dbConn.executeStatement(command);
        } else {
            String msg = "You have to be in a DOCTOR session at this point";
            Log4k.warn(this.getClass().getName(), msg);
        }
    }
    
    private boolean isPatient(int patient_id){
        boolean res = false;
        try {
            ResultSet set;        
            String command = "SELECT * FROM patients WHERE patient_id = " + patient_id;
            set = dbConn.executeQuery(command);        
            res = set.first();            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    private boolean isDoctor(int doctor_id){
        boolean res = false;
        try {
            ResultSet set;        
            String command = "SELECT * FROM health_workers WHERE "
                    + "id = " + doctor_id + " AND NOT is_usl";
            set = dbConn.executeQuery(command);        
            res = set.first();            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    private boolean isChieftain(int patient_id){
        boolean res = false;
        try {
            ResultSet set;        
            String command = "SELECT * FROM patients WHERE "
                    + "patient_id = " + patient_id + " AND chieftain_id = " + patient_id;
            set = dbConn.executeQuery(command);        
            res = set.first();            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    /* whenever a new patient is registered, it is mapped over all the schedules
     * present inside the usl_schedule table that are not new ones. This is
     * becouse we want this patient to have the same usl_schedules programmed
     * as the others, making no distinctions between them.
     * However, it would be a good practise to call this function after
     * having all the patient updated to the new schedules (i.e. after
     * calling updateMedicalRecordsWithNewScheduleInformation() and before
     * inserting this patient inside the db).
     * Note that even if this method shall update the db twice atomically,
     * it is not protected by the dbConnector.mutex variable. It is becouse
     * this method is assumed being called from within a synchronized context.
     */
    private void scheduleVisitsForAPatient(int patient_id){
        String old_schedules = "SELECT * FROM usl_schedule WHERE NOT is_new";
        String SQold_schedules = subQuery(old_schedules, "NS");
        
        String periodicDate = DBaddTime(Macro.MINIMUM_LAPSE);
        String periodicKind = "'VACCINATION'";
        String periodicCond = "kind = 'PERIODIC'";
        String information1 = "SELECT patient_id, refDoctor_id, program_id, "
                + periodicDate + " as D, " + periodicKind + " as K "
                + "FROM patients, " + SQold_schedules + " WHERE "
                + "(" + periodicCond + ")" + " AND patient_id = " + patient_id;
        String SQinformation1 = subQuery(information1);
        
        String programmedDate = "lapseordate";
        String programmedKind = "'PROGRAMMED'";
        String programmedCond = "kind = 'PROGRAMMED'";
        String information2 = "SELECT patient_id, refDoctor_id, program_id, "
                + programmedDate + " as D, " + programmedKind + " as K "
                + "FROM patients, " + SQold_schedules + " WHERE "
                + "(" + programmedCond + ")" + " AND patient_id = " + patient_id;
        String SQinformation2 = subQuery(information2);
        
        String bornProgrammedDate = "birthDate + INTERVAL lapseordate SECOND";
        String bornProgrammedKind = "'PROGRAMMED'";
        String bornProgrammedCond = "kind = 'BORN_PROGRAMMED' AND "
                + "enacting_date <= birthDate";
        String information3 = "SELECT patient_id, refDoctor_id, program_id, "
                + bornProgrammedDate + " as D, " + bornProgrammedKind + " as K "
                + "FROM patients, " + SQold_schedules + " WHERE "
                + "(" + bornProgrammedCond + ")" + " AND patient_id = " + patient_id;
        
        String SQinformation3 = subQuery(information3);
        
        /* UNION keyword cannot be used with parenthesied subqueries */
        String information = information1 + " UNION " + information2
                + " UNION " + information3;
        String SQinformation = subQuery(information, "INFO");
        String command = "INSERT INTO medical_records(patient_id, doctor_id, program_id, date, kind) "
                + "SELECT * FROM " + SQinformation;
        dbConn.executeStatement(command);
    }
    
    /*
     * patient is the patient to be registered inside the db.
     * 
     * Every field but the username and coords must not be null, the username
     * is drawn as surname_id, coords are overwritten with the new ones
     * drawn from geonames.
     * The field refDoctorId must refer to an actual doctor,
     * the field chiefTainId must refer to an actual chiefTain
     * and the field parentId must at least refer to an actual patient
     */
    public void registerPatient(PatientBean patient) throws RegisterPatientException{        
        String command;
        String username, password, name, surname, email_addr, cell_number,
                gender, birthDate, birthPlace, address, cap, coords, picture;        
        int parent_id, chieftain_id, refDoctor_id;
        
        if (patient.getName() == null){
            throw new RegisterPatientException("getName");
        } else name = sqlString(patient.getName());
        if (patient.getSurname() == null){
            throw new RegisterPatientException("getSurname");
        } else surname = sqlString(patient.getSurname());        
        if (patient.getUsername() == null){      
            username = sqlString(""); // temporary, we will change it after            
        } else throw new RegisterPatientException("getUsername");        
        if (patient.getPassword() == null){
            throw new RegisterPatientException("getPassword");
        } else password = sqlString(patient.getPassword());
        if (patient.getEmail() == null){
            throw new RegisterPatientException("getEmail");
        } else email_addr = sqlString(patient.getEmail());
        if (patient.getPhone() == null){
            throw new RegisterPatientException("getPhone");
        } else cell_number = sqlString(patient.getPhone());
        if (patient.getGenderEnum() == null){
            throw new RegisterPatientException("getGender");
        } else gender = sqlString(patient.getGenderEnum().toString());
        if (patient.getBirthdate() == null){
            throw new RegisterPatientException("getBirthdate");
        } else birthDate = dbDate(patient.getBirthdate());
        if (patient.getBirthplace() == null){
            throw new RegisterPatientException("getBirthplace");
        } else {
            birthPlace = sqlString(patient.getBirthplace());            
            try {
                patient.setCoords(Geonames.getCoordinates(patient.getBirthplace()));
            } catch (MalformedURLException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (IOException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (ParserConfigurationException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (SAXException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            }
            coords = sqlString(patient.getCoords());
        }
        if (patient.getAddress() == null){
            throw new RegisterPatientException("getAddress");
        } else address = sqlString(patient.getAddress());
        if (patient.getCap() == null){
            throw new RegisterPatientException("getCap");
        } else cap = sqlString(patient.getCap());        
        if (!isPatient(patient.getParentId())){
            throw new RegisterPatientException("getParentId");
        } else parent_id = patient.getParentId();
        if (!isChieftain(patient.getChiefTainId())){
            throw new RegisterPatientException("getChiefTainId");
        } else chieftain_id = patient.getChiefTainId();
        if (!isDoctor(patient.getRefDoctorId())){
            throw new RegisterPatientException("getRefDoctorId");
        } else refDoctor_id = patient.getRefDoctorId();
        if (patient.getPictureName() == null){
            throw new RegisterPatientException("getPictureName");
        } else picture = sqlString(patient.getPictureName());
        
        /* we turn atomic the insertion of the patient and the scheduling of its
         * basic examinations (inserting rows inside medical_records table)
         */
        synchronized(dbConnector.mutex){
            /* since we will schedule all the examinations of the new patient
             * after having it inserted inside the db, it is important to
             * update the medical_records table with the new schedule information
             * before
             */
            
            updateMedicalRecordsWithNewScheduleInformation();
            
            command = "INSERT INTO patients(username, password, name, surname, email_addr, "
                    + "cell_number, gender, birthDate, birthPlace, address, cap, coords, "
                    + "parent_id, chieftain_id, refDoctor_id, picture) "
                    + "VALUES ("
                    + username + ", " + password + ", " + name + ", " + surname + ", "
                    + email_addr + ", " + cell_number + ", " + gender + ", "
                    + birthDate + ", " + birthPlace + ", " + address + ", "
                    + cap + ", " + coords + ", " + parent_id + ", "
                    + chieftain_id + ", " + refDoctor_id + ", " + picture
                    + ");";
            
            dbConn.executeStatementNoLock(command);
            
            /* retrieve the just inserted patient's id */
            int pat_id = -1;
            command = "SELECT max(patient_id) as m FROM patients";
            ResultSet sqlSet = dbConn.executeQuery(command);
            try {
                if (sqlSet.first()){
                    pat_id = sqlSet.getInt("m");
                } else {
                    String err = "registerPatient: patients Ã¨ una db table vuota";
                    Log4k.warn(this.getClass().getName(), err);
                }
            } catch (SQLException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            }
            
            /* update the just inserted row with the new username */
            String pat_username = patient.getSurname() + "_" + pat_id;            
            command = "UPDATE patients SET username = " + sqlString(pat_username) + " "
                    + "WHERE patient_id = " + pat_id;
            dbConn.executeStatementNoLock(command);
            
            /* set id and username into the bean */
            patient.setId(pat_id);
            patient.setUsername(pat_username);
            
            /*  schedule the visits for the just inserted patient */
            scheduleVisitsForAPatient(pat_id);
            
        }
    }
    
    /*
     * patient is the patient to be reset inside the db.
     * Field id has to be set inside the patient bean.
     * 
     * Fields found null are not reset.
     * username, id, password are not resettable.
     * refDoctorId, chiefTainId, parentId are set only if they are actual IDs
     * 
     */
    public void resetPatient(PatientBean patient){
        String command;
        String name, surname, email_addr, cell_number, gender, birthDate,
                birthPlace, address, cap, coords, picture;        
        int parent_id, chieftain_id, refDoctor_id;        
        
        name = ((patient.getName() != null) ? sqlString(patient.getName()) : "");
        surname = ((patient.getSurname() != null) ? sqlString(patient.getSurname()) : "");
        email_addr = ((patient.getEmail() != null) ? sqlString(patient.getEmail()) : "");
        cell_number = ((patient.getPhone() != null) ? sqlString(patient.getPhone()) : "");
        gender = ((patient.getGenderEnum() != null) ? sqlString(patient.getGenderEnum().toString()) : "");
        birthDate = ((patient.getBirthdate() != null) ? dbDate(patient.getBirthdate()) : "");
        if (patient.getBirthplace() != null){
            birthPlace = sqlString(patient.getBirthplace());
            try {
                patient.setCoords(Geonames.getCoordinates(patient.getBirthplace()));
                coords = sqlString(patient.getCoords());
            } catch (MalformedURLException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (IOException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (ParserConfigurationException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            } catch (SAXException ex) {
                Log4k.error(this.getClass().getName(), ex.getMessage());
            }
        } else {
            birthPlace = "";
            coords = "";
        }        
        address = ((patient.getAddress() != null) ? sqlString(patient.getAddress()) : "");
        cap = ((patient.getCap() != null) ? sqlString(patient.getCap()) : "");
        
        
        coords = ((patient.getCoords() != null) ? sqlString(patient.getCoords()) : "");
                
        parent_id = ((isPatient(patient.getParentId())) ? patient.getParentId() : -1);
        chieftain_id = ((isChieftain(patient.getChiefTainId())) ? patient.getChiefTainId() : -1);
        refDoctor_id = ((isDoctor(patient.getRefDoctorId())) ? patient.getRefDoctorId() : -1);
                
        picture = ((patient.getPictureName() != null) ? sqlString(patient.getPictureName()) : "");
        
        command = "UPDATE patients SET "
                + (!name.equals("") ? "name = " + name + ", " : "")
                + (!surname.equals("") ? "surname = " + surname + ", " : "")
                + (!email_addr.equals("") ? "email_addr = " + email_addr + ", " : "")
                + (!cell_number.equals("") ? "cell_number = " + cell_number + ", " : "")
                + (!gender.equals("") ? "gender = " + gender + ", " : "")
                + (!birthDate.equals("") ? "birthDate = " + birthDate + ", " : "")
                + (!birthPlace.equals("") ? "birthPlace = " + birthPlace + ", " : "")
                + (!address.equals("") ? "address = " + address + ", " : "")
                + (!cap.equals("") ? "cap = " + cap + ", " : "")
                + (!coords.equals("") ? "coords = " + coords + ", " : "")
                + ((parent_id != -1) ? "parent_id = " + parent_id + ", " : "")
                + ((chieftain_id != -1) ? "chieftain_id = " + chieftain_id + ", " : "")
                + ((refDoctor_id != -1) ? "refDoctor_id = " + refDoctor_id + ", " : "")
                + (!picture.equals("") ? "picture = " + picture + ", " : "")
                + "patient_id = " + patient.getId() + " " // assure at least a SET entry
                + "WHERE patient_id = " + patient.getId();
        
        dbConn.executeStatement(command);        
    }    
    
    public void changePassword(String username, userKind type, String newPassword){
        String command;   
        boolean isPatient = (type == userKind.PATIENT);
        boolean is_usl = (type == userKind.USL);
        String userTableStr = ((isPatient) ? "patients" : "health_workers");        
        command = "UPDATE " + userTableStr + " "
                + "SET password = " + sqlString(newPassword) + " "
                + "WHERE username = " + sqlString(username) + " AND "
                + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
        dbConn.executeStatement(command);        
    }
    
    public void changeEmail(int id, userKind type, String newEmail){
        String command;   
        boolean isPatient = (type == userKind.PATIENT);
        boolean is_usl = (type == userKind.USL);
        String userTableStr = ((isPatient) ? "patients" : "health_workers");
        String userKindIDStr = ((isPatient) ? "patient_id" : "id");
        command = "UPDATE " + userTableStr + " "
                + "SET email_addr = " + sqlString(newEmail) + " "
                + "WHERE " + userKindIDStr + " = " + id + " AND "
                + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
        dbConn.executeStatement(command);        
    }
    
    public int compareForUrgency(DateTime date, int interval){
        int ret;

        DateTime now = this.now();
        DateTime max = now.plus(0, 0, 0, 0, 0, interval, DayOverflow.FirstDay);
        
        if (date.compareTo(now) < 0){
            /* date is expired */
            ret = -1; 
        }
        else if (date.compareTo(max) > 0){
            /* date is not a urgent date */
            ret = 1;
        } else {
            /* date is urgent since it is going to expire in the given interval */
            ret = 0;
        } 
        
        return ret;        
    } 
    
    /* given a id and a type, a string is returned as "firstname surname" */
    public String getStrNameByID(int id, userKind type){
        String res = "";
        try {
            boolean isPatient = (type == userKind.PATIENT);   
            boolean is_usl = (type == userKind.USL);
            String userTableStr = ((isPatient) ? "patients" : "health_workers");
            String userKindIDStr = ((isPatient) ? "patient_id" : "id");
            String command = "SELECT * FROM " + userTableStr + " WHERE "
                    + userKindIDStr + " = " + id + " AND "
                    + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getString("name") + " " + sqlSet.getString("surname");
            } else {
                String err = "getStrNameByID: id non trovato nel DB (id = " + id + ")";
                Log4k.warn(this.getClass().getName(), err);
            }
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    /* given a id and a type, the email_addr field is returned */
    public String getEmailByID(int id, userKind type){
        String res = "";
        try {
            boolean isPatient = (type == userKind.PATIENT);   
            boolean is_usl = (type == userKind.USL);
            String userTableStr = ((isPatient) ? "patients" : "health_workers");
            String userKindIDStr = ((isPatient) ? "patient_id" : "id");
            String command = "SELECT email_addr FROM " + userTableStr + " WHERE "
                    + userKindIDStr + " = " + id + " AND "
                    + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getString("email_addr");
            } else {
                String err = "getEmailByID: id non trovato nel DB (id = " + id + ")";
                Log4k.warn(this.getClass().getName(), err);
            }            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    /* given a username and a type, the email_addr field is returned */
    public String getEmailByUsername(String username, userKind type){
        String res = "";
        try {
            boolean isPatient = (type == userKind.PATIENT);   
            boolean is_usl = (type == userKind.USL);
            String userTableStr = ((isPatient) ? "patients" : "health_workers");            
            String command = "SELECT email_addr FROM " + userTableStr + " WHERE "
                    + "username = " + sqlString(username) + " AND "
                    + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getString("email_addr");
            } else {
                String err = "getEmailByUsername: username non trovato nel DB "
                        + "(username = " + username + ")";
                Log4k.warn(this.getClass().getName(), err);
            }            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
 
    /* given a cell_number and a type, the corresponding id field is returned */
    public int getIDByCell(String cell, userKind type){
        int res = 0;
        try {
            boolean isPatient = (type == userKind.PATIENT);   
            boolean is_usl = (type == userKind.USL);
            String userTableStr = ((isPatient) ? "patients" : "health_workers");            
            String userKindIDStr = ((isPatient) ? "patient_id" : "id");
            String command = "SELECT " + userKindIDStr + " FROM " + userTableStr + " WHERE "
                    + "cell_number = " + sqlString(cell) + " AND "
                    + ((isPatient) ? "TRUE" : "is_usl = "+is_usl);
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt(userKindIDStr);
            } else {
                String err = "getIDByCell: cell_number " + cell + " non trovato nel DB";
                Log4k.warn(this.getClass().getName(), err);
            }            
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
     
    
    /* given a id (it must belong to a patient), the chieftain_id field is returned */
    public int getChiefTainByID(int id){
        int res = 0;
        try {
            String command = "SELECT chieftain_id FROM patients WHERE patient_id = " + id;                    
            ResultSet sqlSet = dbConn.executeQuery(command);            
            
            if (sqlSet.first()){
                res = sqlSet.getInt("chieftain_id");
            } else {
                String err = "getChiefTainByID: id non trovato nel DB (id = " + id + ")";
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    public boolean isNotified(int record_id){
        boolean res = false;
        try {
            String command = "SELECT is_notified FROM medical_records "
                    + "WHERE record_id = " + record_id;
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getBoolean("is_notified");
            } else {
                String err = "isNotified: non sono presenti visite con id = " + record_id;
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }    
    
    public int getNumberOfPatients(){
        int res = 0;
        try {
            String command = "SELECT count(patient_id) as n FROM patients";
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt("n");
            } else {
                String err = "getNumberOfPatients: nessun paziente nella db table patients";
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    public int getNumberOfPatients(int doctor_id){
        int res = 0;
        try {
            String command = "SELECT count(patient_id) as n FROM patients "
                    + "WHERE refDoctor_id = " + doctor_id;                    
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt("n");
            } else {
                String err = "getNumberOfPatients: nessun paziente nel db ha "
                        + "un dottore con id = " + doctor_id;
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
    
    /* returns the total number of visits */
    public int getNumberOfVisits(int doctor_id){
        int res = 0;
        try {
            String command = "SELECT count(record_id) as n FROM medical_records "
                    + "WHERE doctor_id = " + doctor_id;
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt("n");
            } else {
                String err = "getNumberOfVisits: nessuna visita nel db ha "
                        + "un dottore con id = " + doctor_id;
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
        
    /* returns the number of visits already performed */
    public int getNumberOfDoneVisits(int doctor_id){
        int res = 0;
        try {
            String command = "SELECT count(record_id) as n FROM medical_records "
                    + "WHERE doctor_id = " + doctor_id + " AND VISIT_DONE";
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt("n");
            } else {
                String err = "getNumberOfDoneVisits: nessuna visita nel db ha "
                        + "un dottore con id = " + doctor_id;
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
            
    /* returns the number of visits to be performed yet */
    public int getNumberOfScheduledVisits(int doctor_id){
        int res = 0;
        try {
            String command = "SELECT count(record_id) as n FROM medical_records "
                    + "WHERE doctor_id = " + doctor_id + " AND "
                    + "NOT visit_done AND is_notified";
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = sqlSet.getInt("n");
            } else {
                String err = "getNumberOfScheduledVisits: nessuna visita nel db ha "
                        + "un dottore con id = " + doctor_id;
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;
    }
 
    public DateTime now(){
        DateTime res = null;
        try {
            String command = DBtime();
            ResultSet sqlSet = dbConn.executeQuery(command);
            
            if (sqlSet.first()){
                res = new DateTime(sqlSet.getString("n_o_w"));
            } else {
                String err = "now(): impossible error!!";
                Log4k.warn(this.getClass().getName(), err);
            } 
        } catch (SQLException ex) {
            Log4k.error(this.getClass().getName(), ex.getMessage());
        }
        return res;        
    }

}
